import datetime

import requests
import sqlite3
import json


def get_songs() -> dict:
    response = requests.get(
        url='https://live.mystreamplayer.com/external.php?http%3A%2F%2Fhydra.cdnstream.com%3A8443%2Fice_history.php',
        params={
            'h': 's3.reliastream.com',
            'p': 8004,
            'i': '1521_128',
            'https': 0,
            'f': 'ice',
            'c': 91973,
        }
    )
    return response.json()


def save_music_data(json_data):
    # 创建或连接到 SQLite 数据库
    conn = sqlite3.connect('music_data.db')
    cursor = conn.cursor()
    
    # 创建表（如果尚不存在）
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS music (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        timestamp INTEGER NOT NULL,
        artist TEXT NOT NULL,
        title TEXT NOT NULL
    )
    ''')
    
    # 准备去重后的数据
    unique_data = {(item['time'], item['artist'], item['title']) for item in json_data}
    
    # 插入数据，确保不重复
    for time, artist, title in unique_data:
        timestamp = int(time / 1000)
        dt_object = datetime.datetime.fromtimestamp(timestamp)
        cursor.execute('SELECT 1 FROM music WHERE timestamp = ?', (timestamp,))
        if cursor.fetchone() is None:  # 如果没有找到记录
            cursor.execute('''
            INSERT INTO music (timestamp, artist, title) VALUES (?, ?, ?)
            ''', (timestamp, artist, title))
            print(dt_object, artist, title, '插入完成')
        else:
            print(dt_object, artist, title, '已存在')
    
    # 提交事务
    conn.commit()
    
    # 查询并打印所有数据
    cursor.execute('SELECT * FROM music')
    rows = cursor.fetchall()
    for row in rows:
        id, timestamp, artist, title = row
        # 将时间戳转换为可读日期
        dt_object = datetime.datetime.fromtimestamp(timestamp)  # 如果时间戳是毫秒
        print(f"ID: {id}, \t 日期: {dt_object}, \t 艺术家: {artist}, \t 标题: {title}")
    
    # 关闭连接
    conn.close()


if __name__ == '__main__':
    songs_dict = get_songs()
    # print(json.dumps(songs_dict, indent=4))
    
    save_music_data(songs_dict)
